package jdbcTest;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

public class CreateSqlTest {
	public static void main(String[] args) {
		Connection conn = null;
		List<String> sqlList = new ArrayList<String>();
		List<String> resultList = new ArrayList<String>();
		/*String url = "jdbc:mysql://172.30.30.4:3308/xedk?"
				+ "user=tempread&password=tempreadadmin";*/
		String url = "jdbc:mysql://localhost:3337/xedk?"
				+ "user=xedk&password=admin";
		String queryAllSql = "select column_name,table_name,CONCAT('select \"',table_name,'\" ,\"',column_name,'\",',column_name,' from ',table_name,'"
				+ " where ifnull(',column_name,',\"\")!=\"\"  limit 1; ') from information_schema.columns  where TABLE_SCHEMA = 'xedk' AND IFNULL(CHARACTER_MAXIMUM_LENGTH, 0) < 50"
				+ " AND IFNULL(CHARACTER_MAXIMUM_LENGTH, 0) >=18"
				+ " order by table_schema,table_name ;";

		try {
			Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动

			// 一个Connection代表一个数据库连接
			conn = DriverManager.getConnection(url);
			// Statement里面带有很多方法，比如executeUpdate可以实现插入，更新和删除等
			Statement stmt = conn.createStatement();
			ResultSet resultSet = stmt.executeQuery(queryAllSql);// executeUpdate语句会返回一个受影响的行数，如果返回-1就没有成功
			while(resultSet.next()){
				if(checkKey(resultSet.getString(1),resultSet.getString(2))){
					continue;
				}
				sqlList.add(resultSet.getString(3));
			}
			for(String sql:sqlList){
				if(sql.contains("ciffileshow"))continue;
				System.out.println(sql);
				resultSet = stmt.executeQuery(sql);
				if(resultSet.next()){
					if(checkRegex(resultSet.getString(3))){
						resultList.add(resultSet.getString(1)+" "+resultSet.getString(2)+" "+resultSet.getString(3)+" ");
					}
				}
			}
			stmt.close();
			resultSet.close();
		} catch (SQLException e) {
			System.out.println("MySQL操作错误");
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		File file = new File("D:\\work\\20150803-prd.txt");
		try {
			createFile(file);
			BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), Charset.forName("UTF-8")));
			for(String result:resultList){
				bw.write(result);
				bw.newLine();
			}
            bw.flush();
            bw.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		
	}
	public static boolean checkRegex(String result){
		String regex = "^\\w*((2014|2015)\\d{14,18}$)";
/*		String regex = "^\\w*(\\d{18}$)";
*/		return Pattern.matches(regex, result);
	}
	
	public static boolean createFile(File file) throws IOException {  
        if(! file.exists()) {  
            makeDir(file.getParentFile());  
        }  
        return file.createNewFile();  
    }  
      
    public static void makeDir(File dir) {  
        if(! dir.getParentFile().exists()) {  
            makeDir(dir.getParentFile());  
        }  
        dir.mkdir();  
    }  
	private static Map<String, String> keyMap = new HashMap<String, String>();
	private static Map<String, String> VIEWMap = new HashMap<String, String>();
	static{
		keyMap.put("ADD", "ADD");
		keyMap.put("ALL", "ALL");
		keyMap.put("ALTER", "ALTER");
		keyMap.put("ANALYZE", "ANALYZE");
		keyMap.put("AND", "AND");
		keyMap.put("AS", "AS");
		keyMap.put("ASC", "ASC");
		keyMap.put("ASENSITIVE", "ASENSITIVE");
		keyMap.put("BEFORE", "BEFORE");
		keyMap.put("BETWEEN", "BETWEEN");
		keyMap.put("BIGINT", "BIGINT");
		keyMap.put("BINARY", "BINARY");
		keyMap.put("BLOB", "BLOB");
		keyMap.put("BOTH", "BOTH");
		keyMap.put("BY", "BY");
		keyMap.put("CALL", "CALL");
		keyMap.put("CASCADE", "CASCADE");
		keyMap.put("CASE", "CASE");
		keyMap.put("CHANGE", "CHANGE");
		keyMap.put("CHAR", "CHAR");
		keyMap.put("CHARACTER", "CHARACTER");
		keyMap.put("CHECK", "CHECK");
		keyMap.put("COLLATE", "COLLATE");
		keyMap.put("COLUMN", "COLUMN");
		keyMap.put("CONDITION", "CONDITION");
		keyMap.put("CONNECTION", "CONNECTION");
		keyMap.put("CONSTRAINT", "CONSTRAINT");
		keyMap.put("CONTINUE", "CONTINUE");
		keyMap.put("CONVERT", "CONVERT");
		keyMap.put("CREATE", "CREATE");
		keyMap.put("CROSS", "CROSS");
		keyMap.put("CURRENT_DATE", "CURRENT_DATE");
		keyMap.put("CURRENT_TIME", "CURRENT_TIME");
		keyMap.put("CURRENT_TIMESTAMP", "CURRENT_TIMESTAMP");
		keyMap.put("CURRENT_USER", "CURRENT_USER");
		keyMap.put("CURSOR", "CURSOR");
		keyMap.put("DATABASE", "DATABASE");
		keyMap.put("DATABASES", "DATABASES");
		keyMap.put("DAY_HOUR", "DAY_HOUR");
		keyMap.put("DAY_MICROSECOND", "DAY_MICROSECOND");
		keyMap.put("DAY_MINUTE", "DAY_MINUTE");
		keyMap.put("DAY_SECOND", "DAY_SECOND");
		keyMap.put("DEC", "DEC");
		keyMap.put("DECIMAL", "DECIMAL");
		keyMap.put("DECLARE", "DECLARE");
		keyMap.put("DEFAULT", "DEFAULT");
		keyMap.put("DELAYED", "DELAYED");
		keyMap.put("DELETE", "DELETE");
		keyMap.put("DESC", "DESC");
		keyMap.put("DESCRIBE", "DESCRIBE");
		keyMap.put("DETERMINISTIC", "DETERMINISTIC");
		keyMap.put("DISTINCT", "DISTINCT");
		keyMap.put("DISTINCTROW", "DISTINCTROW");
		keyMap.put("DIV", "DIV");
		keyMap.put("DOUBLE", "DOUBLE");
		keyMap.put("DROP", "DROP");
		keyMap.put("DUAL", "DUAL");
		keyMap.put("EACH", "EACH");
		keyMap.put("ELSE", "ELSE");
		keyMap.put("ELSEIF", "ELSEIF");
		keyMap.put("ENCLOSED", "ENCLOSED");
		keyMap.put("ESCAPED", "ESCAPED");
		keyMap.put("EXISTS", "EXISTS");
		keyMap.put("EXIT", "EXIT");
		keyMap.put("EXPLAIN", "EXPLAIN");
		keyMap.put("FALSE", "FALSE");
		keyMap.put("FETCH", "FETCH");
		keyMap.put("FLOAT", "FLOAT");
		keyMap.put("FLOAT4", "FLOAT4");
		keyMap.put("FLOAT8", "FLOAT8");
		keyMap.put("FOR", "FOR");
		keyMap.put("FORCE", "FORCE");
		keyMap.put("FOREIGN", "FOREIGN");
		keyMap.put("FROM", "FROM");
		keyMap.put("FULLTEXT", "FULLTEXT");
		keyMap.put("GOTO", "GOTO");
		keyMap.put("GRANT", "GRANT");
		keyMap.put("GROUP", "GROUP");
		keyMap.put("HAVING", "HAVING");
		keyMap.put("HIGH_PRIORITY", "HIGH_PRIORITY");
		keyMap.put("HOUR_MICROSECOND", "HOUR_MICROSECOND");
		keyMap.put("HOUR_MINUTE", "HOUR_MINUTE");
		keyMap.put("HOUR_SECOND", "HOUR_SECOND");
		keyMap.put("IF", "IF");
		keyMap.put("IGNORE", "IGNORE");
		keyMap.put("IN", "IN");
		keyMap.put("INDEX", "INDEX");
		keyMap.put("INFILE", "INFILE");
		keyMap.put("INNER", "INNER");
		keyMap.put("INOUT", "INOUT");
		keyMap.put("INSENSITIVE", "INSENSITIVE");
		keyMap.put("INSERT", "INSERT");
		keyMap.put("INT", "INT");
		keyMap.put("INT1", "INT1");
		keyMap.put("INT2", "INT2");
		keyMap.put("INT3", "INT3");
		keyMap.put("INT4", "INT4");
		keyMap.put("INT8", "INT8");
		keyMap.put("INTEGER", "INTEGER");
		keyMap.put("INTERVAL", "INTERVAL");
		keyMap.put("INTO", "INTO");
		keyMap.put("IS", "IS");
		keyMap.put("ITERATE", "ITERATE");
		keyMap.put("JOIN", "JOIN");
		keyMap.put("KEY", "KEY");
		keyMap.put("KEYS", "KEYS");
		keyMap.put("KILL", "KILL");
		keyMap.put("LABEL", "LABEL");
		keyMap.put("LEADING", "LEADING");
		keyMap.put("LEAVE", "LEAVE");
		keyMap.put("LEFT", "LEFT");
		keyMap.put("LIKE", "LIKE");
		keyMap.put("LIMIT", "LIMIT");
		keyMap.put("LINEAR", "LINEAR");
		keyMap.put("LINES", "LINES");
		keyMap.put("LOAD", "LOAD");
		keyMap.put("LOCALTIME", "LOCALTIME");
		keyMap.put("LOCALTIMESTAMP", "LOCALTIMESTAMP");
		keyMap.put("LOCK", "LOCK");
		keyMap.put("LONG", "LONG");
		keyMap.put("LONGBLOB", "LONGBLOB");
		keyMap.put("LONGTEXT", "LONGTEXT");
		keyMap.put("LOOP", "LOOP");
		keyMap.put("LOW_PRIORITY", "LOW_PRIORITY");
		keyMap.put("MATCH", "MATCH");
		keyMap.put("MEDIUMBLOB", "MEDIUMBLOB");
		keyMap.put("MEDIUMINT", "MEDIUMINT");
		keyMap.put("MEDIUMTEXT", "MEDIUMTEXT");
		keyMap.put("MIDDLEINT", "MIDDLEINT");
		keyMap.put("MINUTE_MICROSECOND", "MINUTE_MICROSECOND");
		keyMap.put("MINUTE_SECOND", "MINUTE_SECOND");
		keyMap.put("MOD", "MOD");
		keyMap.put("MODIFIES", "MODIFIES");
		keyMap.put("NATURAL", "NATURAL");
		keyMap.put("NOT", "NOT");
		keyMap.put("NO_WRITE_TO_BINLOG", "NO_WRITE_TO_BINLOG");
		keyMap.put("NULL", "NULL");
		keyMap.put("NUMERIC", "NUMERIC");
		keyMap.put("ON", "ON");
		keyMap.put("OPTIMIZE", "OPTIMIZE");
		keyMap.put("OPTION", "OPTION");
		keyMap.put("OPTIONALLY", "OPTIONALLY");
		keyMap.put("OR", "OR");
		keyMap.put("ORDER", "ORDER");
		keyMap.put("OUT", "OUT");
		keyMap.put("OUTER", "OUTER");
		keyMap.put("OUTFILE", "OUTFILE");
		keyMap.put("PRECISION", "PRECISION");
		keyMap.put("PRIMARY", "PRIMARY");
		keyMap.put("PROCEDURE", "PROCEDURE");
		keyMap.put("PURGE", "PURGE");
		keyMap.put("RAID0", "RAID0");
		keyMap.put("RANGE", "RANGE");
		keyMap.put("READ", "READ");
		keyMap.put("READS", "READS");
		keyMap.put("REAL", "REAL");
		keyMap.put("REFERENCES", "REFERENCES");
		keyMap.put("REGEXP", "REGEXP");
		keyMap.put("RELEASE", "RELEASE");
		keyMap.put("RENAME", "RENAME");
		keyMap.put("REPEAT", "REPEAT");
		keyMap.put("REPLACE", "REPLACE");
		keyMap.put("REQUIRE", "REQUIRE");
		keyMap.put("RESTRICT", "RESTRICT");
		keyMap.put("RETURN", "RETURN");
		keyMap.put("REVOKE", "REVOKE");
		keyMap.put("RIGHT", "RIGHT");
		keyMap.put("RLIKE", "RLIKE");
		keyMap.put("SCHEMA", "SCHEMA");
		keyMap.put("SCHEMAS", "SCHEMAS");
		keyMap.put("SECOND_MICROSECOND", "SECOND_MICROSECOND");
		keyMap.put("SELECT", "SELECT");
		keyMap.put("SENSITIVE", "SENSITIVE");
		keyMap.put("SEPARATOR", "SEPARATOR");
		keyMap.put("SET", "SET");
		keyMap.put("SHOW", "SHOW");
		keyMap.put("SMALLINT", "SMALLINT");
		keyMap.put("SPATIAL", "SPATIAL");
		keyMap.put("SPECIFIC", "SPECIFIC");
		keyMap.put("SQL", "SQL");
		keyMap.put("SQLEXCEPTION", "SQLEXCEPTION");
		keyMap.put("SQLSTATE", "SQLSTATE");
		keyMap.put("SQLWARNING", "SQLWARNING");
		keyMap.put("SQL_BIG_RESULT", "SQL_BIG_RESULT");
		keyMap.put("SQL_CALC_FOUND_ROWS", "SQL_CALC_FOUND_ROWS");
		keyMap.put("SQL_SMALL_RESULT", "SQL_SMALL_RESULT");
		keyMap.put("SSL", "SSL");
		keyMap.put("STARTING", "STARTING");
		keyMap.put("STRAIGHT_JOIN", "STRAIGHT_JOIN");
		keyMap.put("TABLE", "TABLE");
		keyMap.put("TERMINATED", "TERMINATED");
		keyMap.put("THEN", "THEN");
		keyMap.put("TINYBLOB", "TINYBLOB");
		keyMap.put("TINYINT", "TINYINT");
		keyMap.put("TINYTEXT", "TINYTEXT");
		keyMap.put("TO", "TO");
		keyMap.put("TRAILING", "TRAILING");
		keyMap.put("TRIGGER", "TRIGGER");
		keyMap.put("TRUE", "TRUE");
		keyMap.put("UNDO", "UNDO");
		keyMap.put("UNION", "UNION");
		keyMap.put("UNIQUE", "UNIQUE");
		keyMap.put("UNLOCK", "UNLOCK");
		keyMap.put("UNSIGNED", "UNSIGNED");
		keyMap.put("UPDATE", "UPDATE");
		keyMap.put("USAGE", "USAGE");
		keyMap.put("USE", "USE");
		keyMap.put("USING", "USING");
		keyMap.put("UTC_DATE", "UTC_DATE");
		keyMap.put("UTC_TIME", "UTC_TIME");
		keyMap.put("UTC_TIMESTAMP", "UTC_TIMESTAMP");
		keyMap.put("VALUES", "VALUES");
		keyMap.put("VARBINARY", "VARBINARY");
		keyMap.put("VARCHAR", "VARCHAR");
		keyMap.put("VARCHARACTER", "VARCHARACTER");
		keyMap.put("VARYING", "VARYING");
		keyMap.put("WHEN", "WHEN");
		keyMap.put("WHERE", "WHERE");
		keyMap.put("WHILE", "WHILE");
		keyMap.put("WITH", "WITH");
		keyMap.put("WRITE", "WRITE");
		keyMap.put("X509", "X509");
		keyMap.put("XOR", "XOR");
		keyMap.put("YEAR_MONTH", "YEAR_MONTH");
		keyMap.put("ZEROFILL", "ZEROFILL");
		
		
		VIEWMap.put("beforeafterwarn", "beforeafterwarn");
		VIEWMap.put("blacklist_tel", "blacklist_tel");
		VIEWMap.put("checkpact_tel", "checkpact_tel");
		VIEWMap.put("ciffileshow", "ciffileshow");
		VIEWMap.put("ciftel", "ciftel");
		VIEWMap.put("customerdata_cor_tel", "customerdata_cor_tel");
		VIEWMap.put("customerdata_tel", "customerdata_tel");
		VIEWMap.put("dywarning_tel", "dywarning_tel");
		VIEWMap.put("empapporve", "empapporve");
		VIEWMap.put("financeapp_tel_spi", "financeapp_tel_spi");
		VIEWMap.put("firstwaring_tel", "firstwaring_tel");
		VIEWMap.put("firstwaring_tel_temp", "firstwaring_tel_temp");
		VIEWMap.put("firstwaring_tel_temp2", "firstwaring_tel_temp2");
		VIEWMap.put("fkqr", "fkqr");
		VIEWMap.put("getcifallborrow", "getcifallborrow");
		VIEWMap.put("getcifallloan", "getcifallloan");
		VIEWMap.put("hkqr", "hkqr");
		VIEWMap.put("leave_spi", "leave_spi");
		VIEWMap.put("loan_fee_search", "loan_fee_search");
		VIEWMap.put("loan_spi", "loan_spi");
		VIEWMap.put("thirdpart_transa_flow", "thirdpart_transa_flow");
		VIEWMap.put("v_cr_unfingrade", "v_cr_unfingrade");
		VIEWMap.put("v_cr_unfinweight", "v_cr_unfinweight");
		VIEWMap.put("view_achievements", "view_achievements");
		VIEWMap.put("view_aclnno", "view_aclnno");
		VIEWMap.put("view_applycif", "view_applycif");
		VIEWMap.put("view_basiccifinfo", "view_basiccifinfo");
		VIEWMap.put("view_cifbasci", "view_cifbasci");
		VIEWMap.put("view_ciffamliy", "view_ciffamliy");
		VIEWMap.put("view_datasource", "view_datasource");
		VIEWMap.put("view_financialmoneyinfo", "view_financialmoneyinfo");
		VIEWMap.put("view_kehudiz", "view_kehudiz");
		VIEWMap.put("view_kkcgcifno", "view_kkcgcifno");
		VIEWMap.put("view_lianxieren", "view_lianxieren");
		VIEWMap.put("view_loanmoneyinfo", "view_loanmoneyinfo");
		VIEWMap.put("view_retruninfo", "view_retruninfo");
		VIEWMap.put("view_retrunplan", "view_retrunplan");
		VIEWMap.put("view_returnamtlog", "view_returnamtlog");
		VIEWMap.put("view_wycifno", "view_wycifno");
		VIEWMap.put("view_zchkcifno", "view_zchkcifno");
		VIEWMap.put("view_zhiyexinx", "view_zhiyexinx");
		VIEWMap.put("view_zhiyexinxi", "view_zhiyexinxi");
		VIEWMap.put("waitingpact_tel", "waitingpact_tel");
		VIEWMap.put("zywarning_tel", "zywarning_tel");
		
		
	}
	public static boolean checkKey(String key,String tableName){
		if(keyMap.containsKey(key.toUpperCase())) return true;
		if(VIEWMap.containsKey(tableName)) return true;
		return false;
	}
}
